<?php
/**
 * Created by PhpStorm.
 * User: luzy
 * Date: 2018/1/31
 * Time: 上午11:11
 */

class Weixin_stat_model extends Base_Model
{
    private $_appid;
    private $_appsecret;
    private $_wx_type;

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }
    private function getSourceConfig($source)
    {
        if (!empty($source)){
            $this->config->load('weixin');
            $this->_appid = $this->config->item($source.'_appid');
            $this->_appsecret = $this->config->item($source.'_appsecret');
            $this->_wx_type = $this->config->item($source.'_type');
        }
    }
    //用户增减数据
    public function getUserSummary($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $week=24*3600*7;//微信官方要求的一次请求最大时间跨度
        $stop=$start+$week;
        $dur=$end-$start;
        $loop=$dur<$week?1:ceil($dur/$week);

        $this->getSourceConfig($source);
        $this->load->library("wxinterface/Wxinterface",array("appid"=>$this->_appid,"appsecret"=>$this->_appsecret,"wxtype"=>$this->_wx_type));

        while (true){
            if ($loop == 0||$loop < 0) break;

            $reqstart = date('Y-m-d',$start);
            $reqend = date('Y-m-d',($stop-1));//匹配微信的时间规则
            $res=$this->wxinterface->getUserSummary($reqstart,$reqend);

            if (!isset($res['list'])) {var_dump($res);exit();}//接口请求错误

            if (!empty($res['list'])){

                echo $reqstart.'~'.$reqend.' complete!'.PHP_EOL;

                foreach ($res['list'] as $row) {
                    $data = array();
                    $data['ref_date'] = $row['ref_date'];
                    $data['ref_date_timestamp'] = strtotime($row['ref_date'])+1;
                    $data['user_source'] = (int)$row['user_source'];
                    $data['new_user'] = (int)$row['new_user'];
                    $data['cancel_user'] = (int)$row['cancel_user'];
                    $data['wxmp']  = $this->_wx_type;

                    $this->db->reconnect();
                    $this->db->trans_begin();
                    //更新数据
                    $dataid = $this->db->select('id')->from('wx_stat_user_summary')
                        ->where('ref_date=',$data['ref_date'])
                        ->where('user_source=',$data['user_source'])
                        ->where('wxmp=',$data['wxmp'])
                        ->get()->row_array();
                    if (!empty($dataid)){
                        $this->db->update('wx_stat_user_summary',$data,array('id'=>$dataid['id']));
                    }else{
                        $this->db->insert('wx_stat_user_summary',$data);
                    }

                    if ($this->db->trans_status() === FALSE)
                    {$this->db->trans_rollback();echo 'db insert error'.PHP_EOL;exit();}
                    else {$this->db->trans_commit();}
                }
            }

            $loop--;
            $start+=$week;
            $stop +=$week;
        }
    }
    //用户增减数据
    public function getUserCumulate($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $week=24*3600*7;//微信官方要求的一次请求最大时间跨度
        $stop=$start+$week;
        $dur=$end-$start;
        $loop=$dur<$week?1:ceil($dur/$week);

        $this->getSourceConfig($source);
        $this->load->library("wxinterface/Wxinterface",array("appid"=>$this->_appid,"appsecret"=>$this->_appsecret,"wxtype"=>$this->_wx_type));

        while (true){
            if ($loop == 0||$loop < 0) break;

            $reqstart = date('Y-m-d',$start);
            $reqend = date('Y-m-d',($stop-1));//匹配微信的时间规则
            $res=$this->wxinterface->getUserCumulate($reqstart,$reqend);

            if (!isset($res['list'])) {var_dump($res);exit();}//接口请求错误

            if (!empty($res['list'])){

                echo $reqstart.'~'.$reqend.' complete!'.PHP_EOL;

                foreach ($res['list'] as $row) {
                    $data = array();
                    $data['ref_date'] = $row['ref_date'];
                    $data['ref_date_timestamp'] = strtotime($row['ref_date'])+1;
                    $data['cumulate_user'] = (int)$row['cumulate_user'];
                    $data['wxmp']  = $this->_wx_type;

                    $this->db->reconnect();
                    $this->db->trans_begin();
                    //更新数据
                    $dataid = $this->db->select('id')->from('wx_stat_user_cumulate')
                        ->where('ref_date=',$data['ref_date'])
                        ->where('wxmp=',$data['wxmp'])
                        ->get()->row_array();
                    if (!empty($dataid)){
                        $this->db->update('wx_stat_user_cumulate',$data,array('id'=>$dataid['id']));
                    }else{
                        $this->db->insert('wx_stat_user_cumulate',$data);
                    }

                    if ($this->db->trans_status() === FALSE)
                    {$this->db->trans_rollback();echo 'db insert error'.PHP_EOL;exit();}
                    else {$this->db->trans_commit();}
                }
            }

            $loop--;
            $start+=$week;
            $stop +=$week;
        }
    }
    //获取图文群发总数据
    public function getArticleTotal($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $day=24*3600;//微信官方要求的一次请求最大时间跨度
        $stop=$start+$day;
        $dur=$end-$start;
        $loop=$dur<$day?1:ceil($dur/$day);

        $this->getSourceConfig($source);
        $this->load->library("wxinterface/Wxinterface",array("appid"=>$this->_appid,"appsecret"=>$this->_appsecret,"wxtype"=>$this->_wx_type));

        while (true){
            if ($loop == 0||$loop < 0) break;

            $reqstart = date('Y-m-d',$start);
            $reqend = date('Y-m-d',($stop-1));//匹配微信的时间规则
            $res=$this->wxinterface->getArticleTotal($reqstart,$reqend);

            if (!isset($res['list'])) {var_dump($res);exit();}//接口请求错误

            if (!empty($res['list'])){
                echo $reqstart.'~'.$reqend.' complete!'.PHP_EOL;
                //遍历该日期下群发的所有文章
                foreach ($res['list'] as $row) {
                    $refdate = $row['ref_date'];//文章发送日期
                    $msgid = $row['msgid'];//图文消息id_群发次序
                    $title = $row['title'];//文章标题
                    $wxmp  = $this->_wx_type;//公众号

                    //遍历该文章的数据
                    foreach ($row['details'] as $d){
                        $data = array();
                        $data['ref_date']=$refdate;
                        $data['ref_date_timestamp'] = strtotime($row['ref_date'])+1;
                        $data['msgid']=$msgid;
                        $data['title']=$title;
                        $data['wxmp']=$wxmp;

                        $data['stat_date']=$d['stat_date'];//统计的日期
                        $data['target_user']=(int)$d['target_user'];//送达人数

                        $data['int_page_read_user']=(int)$d['int_page_read_user'];//图文页（点击群发图文卡片进入的页面）的阅读人数
                        $data['int_page_read_count']=(int)$d['int_page_read_count'];//图文页的阅读次数

                        $data['ori_page_read_user']=(int)$d['ori_page_read_user'];//原文页（点击图文页“阅读原文”进入的页面）的阅读人数
                        $data['ori_page_read_count']=(int)$d['ori_page_read_count'];//原文页的阅读次数

                        $data['share_user']=(int)$d['share_user'];//分享的人数
                        $data['share_count']=(int)$d['share_count'];//分享的次数

                        $data['add_to_fav_user']=(int)$d['add_to_fav_user'];//收藏的人数
                        $data['add_to_fav_count']=(int)$d['add_to_fav_count'];//收藏的次数
                        $data['int_page_from_session_read_user']=(int)$d['int_page_from_session_read_user'];//公众号会话阅读人数
                        $data['int_page_from_session_read_count']=(int)$d['int_page_from_session_read_count'];//公众号会话阅读次数

                        $data['int_page_from_hist_msg_read_user']=(int)$d['int_page_from_hist_msg_read_user'];//历史消息页阅读人数
                        $data['int_page_from_hist_msg_read_count']=(int)$d['int_page_from_hist_msg_read_count'];//历史消息页阅读次数

                        $data['int_page_from_feed_read_user']=(int)$d['int_page_from_feed_read_user'];//朋友圈阅读人数
                        $data['int_page_from_feed_read_count']=(int)$d['int_page_from_feed_read_count'];//朋友圈阅读次数

                        $data['int_page_from_friends_read_user']=(int)$d['int_page_from_friends_read_user'];//好友转发阅读人数
                        $data['int_page_from_friends_read_count']=(int)$d['int_page_from_friends_read_count'];//好友转发阅读次数

                        $data['int_page_from_other_read_user']=(int)$d['int_page_from_other_read_user'];//其他场景阅读人数
                        $data['int_page_from_other_read_count']=(int)$d['int_page_from_other_read_count'];//其他场景阅读次数

                        $data['feed_share_from_session_user']=(int)$d['feed_share_from_session_user'];//公众号会话转发朋友圈人数
                        $data['feed_share_from_session_cnt']=(int)$d['feed_share_from_session_cnt'];//公众号会话转发朋友圈次数

                        $data['feed_share_from_feed_user']=(int)$d['feed_share_from_feed_user'];//朋友圈转发朋友圈人数
                        $data['feed_share_from_feed_cnt']=(int)$d['feed_share_from_feed_cnt'];//朋友圈转发朋友圈次数

                        $data['feed_share_from_other_user']=(int)$d['feed_share_from_other_user'];//朋友圈转发朋友圈次数
                        $data['feed_share_from_other_cnt']=(int)$d['feed_share_from_other_cnt'];//其他场景转发朋友圈次数

                        $this->db->reconnect();
                        $this->db->trans_begin();
                        //更新数据
                        $dataid = $this->db->select('id')->from('wx_stat_article_total')
                            ->where('msgid=',$data['msgid'])
                            ->where('wxmp=',$data['wxmp'])
                            ->where('stat_date=',$data['stat_date'])
                            ->get()->row_array();
                        if (!empty($dataid)){
                            $this->db->update('wx_stat_article_total',$data,array('id'=>$dataid['id']));
                        }else{
                            $this->db->insert('wx_stat_article_total',$data);
                        }

                        if ($this->db->trans_status() === FALSE)
                        {$this->db->trans_rollback();echo 'db insert error'.PHP_EOL;exit();}
                        else {$this->db->trans_commit();}
                    }
                }
            }

            $loop--;
            $start+=$day;
            $stop +=$day;
        }
    }
    //获取图文群发总数据
    public function getArticleSummary($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $day=24*3600;//微信官方要求的一次请求最大时间跨度
        $stop=$start+$day;
        $dur=$end-$start;
        $loop=$dur<$day?1:ceil($dur/$day);

        $this->getSourceConfig($source);
        $this->load->library("wxinterface/Wxinterface",array("appid"=>$this->_appid,"appsecret"=>$this->_appsecret,"wxtype"=>$this->_wx_type));

        while (true){
            if ($loop == 0||$loop < 0) break;

            $reqstart = date('Y-m-d',$start);
            $reqend = date('Y-m-d',($stop-1));//匹配微信的时间规则
            $res=$this->wxinterface->getArticleSummary($reqstart,$reqend);

            if (!isset($res['list'])) {var_dump($res);exit();}//接口请求错误

            if (!empty($res['list'])){
                echo $reqstart.'~'.$reqend.' complete!'.PHP_EOL;
                //遍历该日期下群发的所有文章
                foreach ($res['list'] as $row) {
                    $data = array();
                    $data['ref_date']=$row['ref_date'];//统计日期
                    $data['ref_date_timestamp'] = strtotime($row['ref_date'])+1;
                    $data['msgid']=$row['msgid'];//图文消息id_群发次序
                    $data['title']=$row['title'];//文章标题
                    $data['wxmp']=$this->_wx_type;//公众号

                    $data['int_page_read_user']=(int)$row['int_page_read_user'];//图文页（点击群发图文卡片进入的页面）的阅读人数
                    $data['int_page_read_count']=(int)$row['int_page_read_count'];//图文页的阅读次数

                    $data['ori_page_read_user']=(int)$row['ori_page_read_user'];//原文页（点击图文页“阅读原文”进入的页面）的阅读人数
                    $data['ori_page_read_count']=(int)$row['ori_page_read_count'];//原文页的阅读次数

                    $data['share_user']=(int)$row['share_user'];//分享的人数
                    $data['share_count']=(int)$row['share_count'];//分享的次数

                    $data['add_to_fav_user']=(int)$row['add_to_fav_user'];//收藏的人数
                    $data['add_to_fav_count']=(int)$row['add_to_fav_count'];//收藏的次数

                    $this->db->reconnect();
                    $this->db->trans_begin();
                    //更新数据
                    $dataid = $this->db->select('id')->from('wx_stat_article_summary')
                        ->where('msgid=',$data['msgid'])
                        ->where('wxmp=',$data['wxmp'])
                        ->where('ref_date=',$data['ref_date'])
                        ->get()->row_array();
                    if (!empty($dataid)){
                        $this->db->update('wx_stat_article_summary',$data,array('id'=>$dataid['id']));
                    }else{
                        $this->db->insert('wx_stat_article_summary',$data);
                    }

                    if ($this->db->trans_status() === FALSE)
                    {$this->db->trans_rollback();echo 'db insert error'.PHP_EOL;exit();}
                    else {$this->db->trans_commit();}
                }
            }

            $loop--;
            $start+=$day;
            $stop +=$day;
        }
    }
    //获取图文统计数据
    public function getUserRead($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $step=24*3600*3;//微信官方要求的一次请求最大时间跨度
        $stop=$start+$step;
        $dur=$end-$start;
        $loop=$dur<$step?1:ceil($dur/$step);

        $this->getSourceConfig($source);
        $this->load->library("wxinterface/Wxinterface",array("appid"=>$this->_appid,"appsecret"=>$this->_appsecret,"wxtype"=>$this->_wx_type));

        while (true){
            if ($loop == 0||$loop < 0) break;

            $reqstart = date('Y-m-d',$start);
            $reqend = date('Y-m-d',($stop-1));//匹配微信的时间规则
            $res=$this->wxinterface->getUserRead($reqstart,$reqend);

            if (!isset($res['list'])) {var_dump($res);exit();}//接口请求错误

            if (!empty($res['list'])){
                echo $reqstart.'~'.$reqend.' complete!'.PHP_EOL;
                //遍历该日期下群发的所有文章
                foreach ($res['list'] as $row) {
                    $data = array();
                    $data['ref_date']=$row['ref_date'];//统计日期
                    $data['ref_date_timestamp'] = strtotime($row['ref_date'])+1;
                    $data['user_source']=(int)$row['user_source'];//阅读渠道
                    $data['wxmp']=$this->_wx_type;//公众号

                    $data['int_page_read_user']=(int)$row['int_page_read_user'];//图文页（点击群发图文卡片进入的页面）的阅读人数
                    $data['int_page_read_count']=(int)$row['int_page_read_count'];//图文页的阅读次数

                    $data['ori_page_read_user']=(int)$row['ori_page_read_user'];//原文页（点击图文页“阅读原文”进入的页面）的阅读人数
                    $data['ori_page_read_count']=(int)$row['ori_page_read_count'];//原文页的阅读次数

                    $data['share_user']=(int)$row['share_user'];//分享的人数
                    $data['share_count']=(int)$row['share_count'];//分享的次数

                    $data['add_to_fav_user']=(int)$row['add_to_fav_user'];//收藏的人数
                    $data['add_to_fav_count']=(int)$row['add_to_fav_count'];//收藏的次数

                    $this->db->reconnect();
                    $this->db->trans_begin();
                    //更新数据
                    $dataid = $this->db->select('id')->from('wx_stat_user_read')
                        ->where('wxmp=',$data['wxmp'])
                        ->where('ref_date=',$data['ref_date'])
                        ->where('user_source=',$data['user_source'])
                        ->get()->row_array();
                    if (!empty($dataid)){
                        $this->db->update('wx_stat_user_read',$data,array('id'=>$dataid['id']));
                    }else{
                        $this->db->insert('wx_stat_user_read',$data);
                    }

                    if ($this->db->trans_status() === FALSE)
                    {$this->db->trans_rollback();echo 'db insert error'.PHP_EOL;exit();}
                    else {$this->db->trans_commit();}
                }
            }

            $loop--;
            $start+=$step;
            $stop +=$step;
        }
    }
    //获取图文统计分时数据
    public function getUserReadHour($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $step=24*3600;//微信官方要求的一次请求最大时间跨度
        $stop=$start+$step;
        $dur=$end-$start;
        $loop=$dur<$step?1:ceil($dur/$step);

        $this->getSourceConfig($source);
        $this->load->library("wxinterface/Wxinterface",array("appid"=>$this->_appid,"appsecret"=>$this->_appsecret,"wxtype"=>$this->_wx_type));

        while (true){
            if ($loop == 0||$loop < 0) break;

            $reqstart = date('Y-m-d',$start);
            $reqend = date('Y-m-d',($stop-1));//匹配微信的时间规则
            $res=$this->wxinterface->getUserReadHour($reqstart,$reqend);

            if (!isset($res['list'])) {var_dump($res);exit();}//接口请求错误

            if (!empty($res['list'])){
                echo $reqstart.'~'.$reqend.' complete!'.PHP_EOL;
                //遍历该日期下群发的所有文章
                foreach ($res['list'] as $row) {
                    $data = array();
                    $data['ref_date']=$row['ref_date'];//统计日期
                    $data['ref_date_timestamp'] = strtotime($row['ref_date'])+1;
                    $data['ref_hour']=(int)$row['ref_hour'];//统计时刻
                    $data['user_source']=(int)$row['user_source'];//阅读渠道
                    $data['wxmp']=$this->_wx_type;//公众号

                    $data['int_page_read_user']=(int)$row['int_page_read_user'];//图文页（点击群发图文卡片进入的页面）的阅读人数
                    $data['int_page_read_count']=(int)$row['int_page_read_count'];//图文页的阅读次数

                    $data['ori_page_read_user']=(int)$row['ori_page_read_user'];//原文页（点击图文页“阅读原文”进入的页面）的阅读人数
                    $data['ori_page_read_count']=(int)$row['ori_page_read_count'];//原文页的阅读次数

                    $data['share_user']=(int)$row['share_user'];//分享的人数
                    $data['share_count']=(int)$row['share_count'];//分享的次数

                    $data['add_to_fav_user']=(int)$row['add_to_fav_user'];//收藏的人数
                    $data['add_to_fav_count']=(int)$row['add_to_fav_count'];//收藏的次数

                    $this->db->reconnect();
                    $this->db->trans_begin();
                    //更新数据
                    $dataid = $this->db->select('id')->from('wx_stat_user_read_hour')
                        ->where('wxmp=',$data['wxmp'])
                        ->where('ref_date=',$data['ref_date'])
                        ->where('ref_hour=',$data['ref_hour'])
                        ->where('user_source',$data['user_source'])
                        ->get()->row_array();
                    if (!empty($dataid)){
                        $this->db->update('wx_stat_user_read_hour',$data,array('id'=>$dataid['id']));
                    }else{
                        $this->db->insert('wx_stat_user_read_hour',$data);
                    }

                    if ($this->db->trans_status() === FALSE)
                    {$this->db->trans_rollback();echo 'db insert error'.PHP_EOL;exit();}
                    else {$this->db->trans_commit();}
                }
            }

            $loop--;
            $start+=$step;
            $stop +=$step;
        }
    }
    //获取图文分享转发数据
    public function getUserShare($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $step=24*3600*7;//微信官方要求的一次请求最大时间跨度
        $stop=$start+$step;
        $dur=$end-$start;
        $loop=$dur<$step?1:ceil($dur/$step);

        $this->getSourceConfig($source);
        $this->load->library("wxinterface/Wxinterface",array("appid"=>$this->_appid,"appsecret"=>$this->_appsecret,"wxtype"=>$this->_wx_type));

        while (true){
            if ($loop == 0||$loop < 0) break;

            $reqstart = date('Y-m-d',$start);
            $reqend = date('Y-m-d',($stop-1));//匹配微信的时间规则
            $res=$this->wxinterface->getUserShare($reqstart,$reqend);

            if (!isset($res['list'])) {var_dump($res);exit();}//接口请求错误

            if (!empty($res['list'])){
                echo $reqstart.'~'.$reqend.' complete!'.PHP_EOL;
                //遍历该日期下群发的所有文章
                foreach ($res['list'] as $row) {
                    $data = array();
                    $data['ref_date']=$row['ref_date'];//统计日期
                    $data['ref_date_timestamp'] = strtotime($row['ref_date'])+1;
                    $data['share_scene']=(int)$row['share_scene'];//阅读渠道
                    $data['wxmp']=$this->_wx_type;//公众号

                    $data['share_user']=(int)$row['share_user'];//分享的人数
                    $data['share_count']=(int)$row['share_count'];//分享的次数

                    $this->db->reconnect();
                    $this->db->trans_begin();
                    //更新数据
                    $dataid = $this->db->select('id')->from('wx_stat_user_share')
                        ->where('wxmp=',$data['wxmp'])
                        ->where('ref_date=',$data['ref_date'])
                        ->where('share_scene',$data['share_scene'])
                        ->get()->row_array();
                    if (!empty($dataid)){
                        $this->db->update('wx_stat_user_share',$data,array('id'=>$dataid['id']));
                    }else{
                        $this->db->insert('wx_stat_user_share',$data);
                    }

                    if ($this->db->trans_status() === FALSE)
                    {$this->db->trans_rollback();echo 'db insert error'.PHP_EOL;exit();}
                    else {$this->db->trans_commit();}
                }
            }

            $loop--;
            $start+=$step;
            $stop +=$step;
        }
    }
    //获取图文分享转发数据
    public function getUserShareHour($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $step=24*3600;//微信官方要求的一次请求最大时间跨度
        $stop=$start+$step;
        $dur=$end-$start;
        $loop=$dur<$step?1:ceil($dur/$step);

        $this->getSourceConfig($source);
        $this->load->library("wxinterface/Wxinterface",array("appid"=>$this->_appid,"appsecret"=>$this->_appsecret,"wxtype"=>$this->_wx_type));

        while (true){
            if ($loop == 0||$loop < 0) break;

            $reqstart = date('Y-m-d',$start);
            $reqend = date('Y-m-d',($stop-1));//匹配微信的时间规则
            $res=$this->wxinterface->getUserShareHour($reqstart,$reqend);

            if (!isset($res['list'])) {var_dump($res);exit();}//接口请求错误

            if (!empty($res['list'])){
                echo $reqstart.'~'.$reqend.' complete!'.PHP_EOL;
                //遍历该日期下群发的所有文章
                foreach ($res['list'] as $row) {
                    $data = array();
                    $data['ref_date']=$row['ref_date'];//统计日期
                    $data['ref_date_timestamp'] = strtotime($row['ref_date'])+1;
                    $data['ref_hour']=(int)$row['ref_hour'];//统计时刻
                    $data['share_scene']=(int)$row['share_scene'];//阅读渠道
                    $data['wxmp']=$this->_wx_type;//公众号

                    $data['share_user']=(int)$row['share_user'];//分享的人数
                    $data['share_count']=(int)$row['share_count'];//分享的次数

                    $this->db->reconnect();
                    $this->db->trans_begin();
                    //更新数据
                    $dataid = $this->db->select('id')->from('wx_stat_user_share_hour')
                        ->where('wxmp=',$data['wxmp'])
                        ->where('ref_date=',$data['ref_date'])
                        ->where('ref_hour=',$data['ref_hour'])
                        ->where('share_scene',$data['share_scene'])
                        ->get()->row_array();
                    if (!empty($dataid)){
                        $this->db->update('wx_stat_user_share_hour',$data,array('id'=>$dataid['id']));
                    }else{
                        $this->db->insert('wx_stat_user_share_hour',$data);
                    }

                    if ($this->db->trans_status() === FALSE)
                    {$this->db->trans_rollback();echo 'db insert error'.PHP_EOL;exit();}
                    else {$this->db->trans_commit();}
                }
            }

            $loop--;
            $start+=$step;
            $stop +=$step;
        }
    }
    //以周为粒度统计图文阅读数据
    public function getArticleReadWeekData($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $step=24*3600*7;
        $stop=$start+$step;
        $dur=$end-$start;
        $loop=$dur<$step?1:ceil($dur/$step);

        $data = array();
        //表头
        $data['summary'][] = array("周序号","日期","会话","占比","好友","占比","朋友圈","占比","腾讯微博","占比","历史消息页","占比","其他","占比");
        $data['total'][] = array("周序号","日期","阅读人数","阅读次数","原文阅读人数","原文阅读次数","分享人数","分享次数","收藏人数","收藏次数");
        $weekno = 1;//周序号
        while (true){
            if ($loop == 0||$loop < 0) break;
            $datestr = date('Y-m-d',$start).'~'.date('Y-m-d',$stop);
            //统计一个星期内各个来源的阅读数
            $res = $this->db->select('user_source,SUM(int_page_read_user) as readUser,SUM(int_page_read_count) as readCount,
            SUM(ori_page_read_user) as oriReadUser,SUM(ori_page_read_count) as oriReadCount,
            SUM(share_user) as shareUser,SUM(share_count) as shareCount,
            SUM(add_to_fav_user) as favUser,SUM(add_to_fav_count) as favCount')
                ->from('wx_stat_user_read')
                ->where('wxmp',$source)
                ->where('ref_date_timestamp >',$start)
                ->where('ref_date_timestamp <',$stop)
                ->group_by('user_source')
                ->get()
                ->result_array();

            $datarow = array();
            $datarowformat = array();
            $datarowformat[] = $weekno;
            $datarowformat[] = $datestr;
            $totalread = 0;

            foreach ($res as $row){
                $index = "a".$row['user_source'];
                $read = (int)$row['readUser'];
                $datarow[$index] = $read;
                $totalread += $read;
            }

            for ($i=0;$i<6;$i++){
                $index = "a".$i;
                if (!isset($datarow[$index])){
                    $datarowformat[]=0;
                    $datarowformat[]=0;
                }
                else {
                    $datarowformat[]=$datarow[$index];
                    $datarowformat[]=round($datarow[$index]/$totalread,4);
                }
            }

            $data['summary'][] = $datarowformat;

            //统计一个星期内的总阅读数
            $res = $this->db->select('SUM(int_page_read_user) as readUser,SUM(int_page_read_count) as readCount,
            SUM(ori_page_read_user) as oriReadUser,SUM(ori_page_read_count) as oriReadCount,
            SUM(share_user) as shareUser,SUM(share_count) as shareCount,
            SUM(add_to_fav_user) as favUser,SUM(add_to_fav_count) as favCount')
                ->from('wx_stat_user_read')
                ->where('wxmp',$source)
                ->where('ref_date_timestamp >',$start)
                ->where('ref_date_timestamp <',$stop)
                ->get()
                ->row_array();

            $datarow = array();
            $datarow[] = $weekno;
            $datarow[] = $datestr;
            $datarow[] = isset($res['readUser'])?$res['readUser']:0;
            $datarow[] = isset($res['readCount'])?$res['readCount']:0;
            $datarow[] = isset($res['oriReadUser'])?$res['oriReadUser']:0;
            $datarow[] = isset($res['oriReadCount'])?$res['oriReadCount']:0;
            $datarow[] = isset($res['shareUser'])?$res['shareUser']:0;
            $datarow[] = isset($res['shareCount'])?$res['shareCount']:0;
            $datarow[] = isset($res['favUser'])?$res['favUser']:0;
            $datarow[] = isset($res['favCount'])?$res['favCount']:0;
            $data['total'][] = $datarow;

            $loop--;
            $weekno++;
            $start+=$step;
            $stop +=$step;
        }

        return $data;

    }
    //以周为粒度统计粉丝数据
    public function getUserWeekData($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $step=24*3600*7;
        $stop=$start+$step;
        $dur=$end-$start;
        $loop=$dur<$step?1:ceil($dur/$step);

        $data = array();
        //表头
        $data['total'][] = array("周序号","日期","新增人数","取关人数","用户总数");
        $data['newuser'][] = array("周序号","日期","公众号搜索","占比","名片分享","占比","扫描二维码","占比",
            "图文页右上角菜单","占比","支付后关注","占比","图文页内公众号名称","占比","公众号文章广告","占比",
            "朋友圈广告","占比","其他","占比");
        $weekno = 1;//周序号
        while (true){
            if ($loop == 0||$loop < 0) break;
            $datestr = date('Y-m-d',$start).'~'.date('Y-m-d',$stop);
            //统计一个星期内各个来源的用户新增数
            $res = $this->db->select('user_source,SUM(new_user) as newUser')
                ->from('wx_stat_user_summary')
                ->where('wxmp',$source)
                ->where('ref_date_timestamp >',$start)
                ->where('ref_date_timestamp <',$stop)
                ->group_by('user_source')
                ->get()
                ->result_array();

            $datarow = array();
            $datarowformat = array();
            $datarowformat[] = $weekno;
            $datarowformat[] = $datestr;
            $totalnewuser = 0;

            foreach ($res as $row){
                $index = "a".$row['user_source'];
                $newuser = (int)$row['newUser'];
                $datarow[$index] = $newuser;
                $totalnewuser += $newuser;
            }

            $datarowformat[] = isset($datarow['a1'])?$datarow['a1']:0;
            $datarowformat[] = isset($datarow['a1'])?round($datarow['a1']/$totalnewuser,4):0;
            $datarowformat[] = isset($datarow['a17'])?$datarow['a17']:0;
            $datarowformat[] = isset($datarow['a17'])?round($datarow['a17']/$totalnewuser,4):0;
            $datarowformat[] = isset($datarow['a30'])?$datarow['a30']:0;
            $datarowformat[] = isset($datarow['a30'])?round($datarow['a30']/$totalnewuser,4):0;
            $datarowformat[] = isset($datarow['a43'])?$datarow['a43']:0;
            $datarowformat[] = isset($datarow['a43'])?round($datarow['a43']/$totalnewuser,4):0;
            $datarowformat[] = isset($datarow['a51'])?$datarow['a51']:0;
            $datarowformat[] = isset($datarow['a51'])?round($datarow['a51']/$totalnewuser,4):0;
            $datarowformat[] = isset($datarow['a57'])?$datarow['a57']:0;
            $datarowformat[] = isset($datarow['a57'])?round($datarow['a57']/$totalnewuser,4):0;
            $datarowformat[] = isset($datarow['a75'])?$datarow['a75']:0;
            $datarowformat[] = isset($datarow['a75'])?round($datarow['a75']/$totalnewuser,4):0;
            $datarowformat[] = isset($datarow['a78'])?$datarow['a78']:0;
            $datarowformat[] = isset($datarow['a78'])?round($datarow['a78']/$totalnewuser,4):0;
            $datarowformat[] = isset($datarow['a0'])?$datarow['a0']:0;
            $datarowformat[] = isset($datarow['a0'])?round($datarow['a0']/$totalnewuser,4):0;

            $data['newuser'][] = $datarowformat;

            //统计一个星期内的用户总计增减数
            $res = $this->db->select('SUM(new_user) as newUser,SUM(cancel_user) as cancelUser')
                ->from('wx_stat_user_summary')
                ->where('wxmp',$source)
                ->where('ref_date_timestamp >',$start)
                ->where('ref_date_timestamp <',$stop)
                ->get()
                ->row_array();
            $cumulate = $this->db->select('cumulate_user')
                ->from('wx_stat_user_cumulate')
                ->where('wxmp',$source)
                ->where('ref_date =',date('Y-m-d',($stop-1)))
                ->get()
                ->row_array();

            $datarow = array();
            $datarow[] = $weekno;
            $datarow[] = $datestr;
            $datarow[] = isset($res['newUser'])?$res['newUser']:0;
            $datarow[] = isset($res['cancelUser'])?$res['cancelUser']:0;
            $datarow[] = isset($cumulate['cumulate_user'])?$cumulate['cumulate_user']:0;
            $data['total'][] = $datarow;

            $loop--;
            $weekno++;
            $start+=$step;
            $stop +=$step;
        }

        return $data;
    }
    //更新文章发送信息
    public function updateArticleDataToDB()
    {
        $offset = 0;
        $limit = 10;
        while (true){
            $this->db->reconnect();
            $articles = $this->db->select('msgid,title,wxmp')->from('wx_stat_article_summary')
                ->group_by(array('msgid','title','wxmp'))
                ->order_by('msgid','ASC')
                ->limit($limit,$offset)
                ->get()->result_array();
            if (empty($articles)) break;//遍历完毕跳出

            foreach ($articles as $a){
                //查询文章的发送日期
                $data=$this->db->select('msgid,title,wxmp,ref_date,ref_date_timestamp')->from('wx_stat_article_summary')
                    ->where('msgid=',$a['msgid'])
                    ->where('wxmp=',$a['wxmp'])
                    ->order_by('ref_date_timestamp','ASC')
                    ->get()->row_array();
                //更新数据
                $dataid = $this->db->select('id')->from('wx_stat_article')
                    ->where('wxmp=',$a['wxmp'])
                    ->where('msgid=',$a['msgid'])
                    ->get()->row_array();

                $this->db->trans_begin();
                if (!empty($dataid)){
                    $this->db->update('wx_stat_article',$data,array('id'=>$dataid['id']));
                }else{
                    $this->db->insert('wx_stat_article',$data);
                }

                if ($this->db->trans_status() === FALSE)
                {$this->db->trans_rollback();echo 'db insert error'.PHP_EOL;exit();}
                else {$this->db->trans_commit();}
            }

            $offset+=$limit;
        }

    }
    //统计文章发布当日打开率
    public function getArticleOpenR($source,$start,$end)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $step=24*3600*7;
        $stop=$start+$step;
        $dur=$end-$start;
        $loop=$dur<$step?1:ceil($dur/$step);

        $data = array();
        $data[] = array("标题","发送日期","文章次序","当日粉丝","7日累计阅读人数","公众号阅读人数","当日打开率","公众号阅读次数",
            "历史消息页阅读人数","历史消息页阅读次数","朋友圈阅读人数","朋友圈阅读次数","好友转发阅读人数","好友转发阅读次数",
            "其他场景阅读人数","其他场景阅读次数","公众号会话转发朋友圈人数 ","公众号会话转发朋友圈次数",
            "朋友圈转发朋友圈人数","朋友圈转发朋友圈次数","其他场景转发朋友圈人数","其他场景转发朋友圈次数 ");
        while (true){
            if ($loop == 0||$loop < 0) break;
            //筛选该时间段内发送的文章
            $articles = $this->db->select('msgid,ref_date,title')->from('wx_stat_article')
                ->where('wxmp',$source)
                ->where('ref_date_timestamp >',$start)
                ->where('ref_date_timestamp <',$stop)
                ->get()->result_array();
            foreach ($articles as $a){
                //文章次序
                $articleorder = substr($a['msgid'],-1,1);
                //文章发布当日的阅读数据
                $readdata = $this->db->select('int_page_from_session_read_user as userRead,int_page_from_session_read_count as readCount,
                int_page_from_hist_msg_read_user as histUser,int_page_from_hist_msg_read_count as histCount,
                int_page_from_feed_read_user as feedUser,int_page_from_feed_read_count as feedCount,
                int_page_from_friends_read_user as fUser,int_page_from_friends_read_count as fCount,
                int_page_from_other_read_user as oUser,int_page_from_other_read_count as oCount,
                feed_share_from_session_user as ssUser,feed_share_from_session_cnt as ssCount,
                feed_share_from_feed_user as sfUser,feed_share_from_feed_cnt as sfCount,
                feed_share_from_other_user as soUser,feed_share_from_other_cnt as soCount,
                ')
                    ->from('wx_stat_article_total')
                    ->where('wxmp',$source)
                    ->where('msgid',$a['msgid'])
                    ->where('ref_date',$a['ref_date'])//文章发送日期
                    ->where('stat_date',$a['ref_date'])//文章统计日期
                    ->get()->row_array();
                //文章发送后7日累计阅读人数
                $readdata2=$this->db->select('int_page_read_user as userRead')->from('wx_stat_article_total')
                    ->where('wxmp',$source)
                    ->where('msgid',$a['msgid'])
                    ->where('ref_date',$a['ref_date'])//文章发送日期
                    ->order_by('stat_date_timestamp','DESC')
                    ->get()->row_array();
                //文章发布当日的粉丝数
                $userdata = $this->db->select('cumulate_user')->from('wx_stat_user_cumulate')
                    ->where('ref_date',$a['ref_date'])
                    ->where('wxmp',$source)
                    ->get()->row_array();

                $datarow = array();
                $datarow[] = $a['title'];//标题
                $datarow[] = $a['ref_date'];//发送日期
                $datarow[] = $articleorder;//文章次序
                $datarow[] = $userdata['cumulate_user'];//当日粉丝
                $datarow[] = $readdata2['userRead'];//7日累计阅读人数
                $datarow[] = $readdata['userRead'];//公众号阅读人数
                $datarow[] = round($readdata['userRead']/$userdata['cumulate_user'],4);
                $datarow[] = $readdata['readCount'];
                $datarow[] = $readdata['histUser'];//历史消息页阅读人数
                $datarow[] = $readdata['histCount'];
                $datarow[] = $readdata['feedUser'];//朋友圈阅读人数
                $datarow[] = $readdata['feedCount'];
                $datarow[] = $readdata['fUser'];//好友转发阅读人数
                $datarow[] = $readdata['fCount'];
                $datarow[] = $readdata['oUser'];//其他场景阅读人数
                $datarow[] = $readdata['oCount'];
                $datarow[] = $readdata['ssUser'];//公众号会话转发朋友圈人数
                $datarow[] = $readdata['sfCount'];
                $datarow[] = $readdata['sfUser'];//朋友圈转发朋友圈人数
                $datarow[] = $readdata['sfCount'];
                $datarow[] = $readdata['soUser'];//其他场景转发朋友圈人数
                $datarow[] = $readdata['soCount'];
                $data[] = $datarow;
            }

            $loop--;
            $start+=$step;
            $stop +=$step;
        }

        return $data;
    }
    //统计文章阅读周循环分时数据
    public function getArticleReadHourOfWeek($source,$start,$end,$weekdaystart)
    {
        $start=strtotime($start);
        $end=strtotime($end);
        $step=24*3600*7;
        $day=3600*24;
        $stop=$start+$step;
        $dur=$end-$start;
        $loop=$dur<$step?1:ceil($dur/$step);

        $data = array();
        $data[] = array("星期","日期","00:00-01:00","01:00-02:00","02:00-03:00","03:00-04:00",
            "04:00-05:00","05:00-06:00","06:00-07:00","07:00-08:00","08:00-09:00",
            "09:00-10:00","10:00-11:00","11:00-12:00","12:00-13:00",
            "13:00-14:00","14:00-15:00","15:00-16:00","16:00-17:00",
            "17:00-18:00","18:00-19:00","19:00-20:00","20:00-21:00",
            "21:00-22:00","22:00-23:00","23:00-24:00");

        while (true){
            if ($loop == 0||$loop < 0) break;
            $res = $this->db->select('ref_date,ref_hour,SUM(int_page_read_user) AS readUser')
                ->from('wx_stat_user_read_hour')
                ->where('wxmp',$source)
                ->where('ref_date_timestamp >',$start)
                ->where('ref_date_timestamp <',$stop)
                ->group_by(array('ref_date','ref_hour'))
                ->get()->result_array();

            $datarow = array();
            $datarowtotal = array();
            foreach ($res as $row){
                $hourstr = "h".$row['ref_hour'];
                $datarow[$row['ref_date']][$hourstr]['readUser'] = $row['readUser'];
                if (isset($datarowtotal[$row['ref_date']]['totalUser'])){
                    $datarowtotal[$row['ref_date']]['totalUser'] += (int)$row['readUser'];
                }else{$datarowtotal[$row['ref_date']]['totalUser'] = 0;}

            }

            for ($i=0;$i<7;$i++){
                //获取循环时间段的日期
                $datetime = $start + $day*$i;
                $datestr = date('Y-m-d',$datetime);
                //获取该日期的星期
                $weekday = ($weekdaystart + $i)%7;
                $weekday = $weekday==0?7:$weekday;
                //重整该日期下的数据
                $datarowformat = array();
                $datarowformat[] = $weekday;
                $datarowformat[] = $datestr;
                //重整该日期下各时段的数据
                for ($h=0;$h<24;$h++){
                    $hourtime = $h*100;
                    $hourstr  = "h".$hourtime;
                    $readuser = isset($datarow[$datestr][$hourstr]['readUser'])?$datarow[$datestr][$hourstr]['readUser']:0;
                    $datarowformat[] = $readuser;
                }

                $data[] = $datarowformat;
            }

            $loop--;
            $start+=$step;
            $stop +=$step;
        }
        return $data;
    }

    public function formatUserSource($source)
    {
        $res = '';
        switch ((int)$source){
            case 0:
                $res = "会话";
                break;
            case 1:
                $res = "好友";
                break;
            case 2:
                $res = "朋友圈";
                break;
            case 3:
                $res = "腾讯微博";
                break;
            case 4:
                $res = "历史消息页";
                break;
            case 5:
                $res = "其他";
                break;
            default:
                $res = false;
        }
        return $res;
    }

    public function format ()
    {
        $offset = 0;
        $limit = 10;
        while(true){
            $res = $this->db->select('id,stat_date')->from('wx_stat_article_total')
                ->order_by('id','ASC')
                ->limit($limit,$offset)
                ->get()->result_array();
            if (empty($res)) break;
            $data = array();
            foreach ($res as $row){
                $datarow = array();
                $datarow['id'] = $row['id'];
                $datarow['stat_date_timestamp'] = strtotime($row['stat_date']) + 1;
                $data[] = $datarow;
            }
            $this->db->update_batch('wx_stat_article_total',$data,'id');
            $offset+=$limit;
        }
    }
}